/**
 * Copyright 2011 Google Inc.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.google.api.adwords.starterkit.examples.enhancedads.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.log4j.Logger;

/**
 * Schedule maps the information about scheduling for each ad into the local
 * database.
 * 
 * @author rodrigo.f.vale@gmail.com
 */
public class Schedule {
  private Connection conn;
  private PreparedStatement update;
  private static final Logger log = Logger.getLogger("StarterKit");
  
  public Schedule(Connection conn) {
    this.conn = conn;
    Statement stat;
    try {
      stat = conn.createStatement();
      stat.executeUpdate(
          "CREATE TABLE IF NOT EXISTS Schedule "
        + "  ( "
        + "     LocalId           INTEGER, "          
        + "     StartDate         INTEGER, "          
        + "     EndDate           INTEGER, "        
        + "     StartHour         INTEGER, "
        + "     EndHour           INTEGER, "        
        + "     Sunday            INTEGER, "
        + "     Monday            INTEGER, "
        + "     Tuesday           INTEGER, "
        + "     Wednesday         INTEGER, "
        + "     Thursday          INTEGER, "
        + "     Friday            INTEGER, "
        + "     Saturday          INTEGER, "  
        + "     AlternativeTextAd BOOLEAN, "
        + "     PRIMARY KEY (LocalId)"
        + "  ); ");
      
      stat.executeUpdate(
          "CREATE INDEX IF NOT EXISTS schedule_date_index " 
        + "on schedule (StartDate, EndDate);");
      
      stat.executeUpdate(
          "CREATE INDEX IF NOT EXISTS schedule_hour_index " 
        + "on Schedule (startHour,endHour);");
      
      update = conn.prepareStatement(
          "REPLACE INTO Schedule (LocalId, StartDate, EndDate, StartHour, " +
          "EndHour, Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, " +
          "Saturday, AlternativeTextAd) " +
          "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");            
      
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public void clear() {
    Statement stat;
    try {
      stat = conn.createStatement();
      stat.executeUpdate("DELETE FROM Schedule");                 
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public void saveBatch(int localId, int startDate, int endDate,
      int startHour, int endHour, int sunday, int monday, int tuesday, 
      int wednesday, int thursday, int friday, int saturday, 
      boolean altTextAd) {
    try {
      update.setInt(1, localId);
      update.setInt(2, startDate);
      update.setInt(3, endDate);
      update.setInt(4, startHour);
      update.setInt(5, endHour);
      update.setInt(6, sunday);
      update.setInt(7, monday);
      update.setInt(8, tuesday);
      update.setInt(9, wednesday);
      update.setInt(10, thursday);
      update.setInt(11, friday);
      update.setInt(12, saturday);
      update.setBoolean(13, altTextAd);
      update.addBatch();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public void executeBatch() {
    log.debug("Executing batch at Schedule");
    try {
      update.executeBatch();                  
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }  
}
